using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.OracleClient;
using System.Globalization;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.CommonHelpers;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.ObjectModel.Base;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.ObjectModel.Oracle;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.Translators;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.Translators.Oracle;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.Translators.Oracle.Data;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.Translators.Oracle.Helpers;
using ODP = Oracle.DataAccess.Client;

namespace Microsoft.Practices.RepositoryFactory.SchemaDiscovery.Plugins
{
    /// <summary>
    /// Represents a Oracle schema provider plugin
    /// </summary>
    internal class OracleSchemaProvider : DbSchemaProvider
    {
        /// <summary>
        /// The userName is the name of the target schema.
        /// </summary>
        private string _userName;

        #region DbSchemaProvider Members
        /// <summary>
        /// Gets the tables.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public override List<Table> GetTables(DbConnection connection)
        {
            // IMPORTANT: Must set the user ID before this public implementation used!
            _userName = GetUserName(connection).ToUpperInvariant();

            List<Table> tables = new List<Table>();
            DataTable tablesCollections = GetTablesCollection(connection);
            DataRow[] rows = tablesCollections.Select("", "owner, table_name ASC");

            foreach (DataRow table in rows)
            {
                OracleTableTranslator translator = new OracleTableTranslator();

                string tableName = NamingHelper.EscapeObjectName(table["table_name"].ToString());

                tables.Add(
                    translator.OracleTableCollectionToTable(
                        table,
                        GetTableColumnsCollection(connection, tableName),
                        GetTableForeignKeysCollection(connection, tableName)));
            }

            return tables;
        }

        /// <summary>
        /// Gets the views.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public override List<View> GetViews(DbConnection connection)
        {
            // IMPORTANT: Must set the user ID before this public implementation used!
            _userName = GetUserName(connection).ToUpperInvariant();

            List<View> views = new List<View>();
            DataTable viewsCollection = GetViewsCollection(connection);
            DataRow[] rows = viewsCollection.Select("", "owner, view_name ASC");

            foreach (DataRow view in rows)
            {
                OracleViewTranslator translator = new OracleViewTranslator();

                string viewName = NamingHelper.EscapeObjectName(view["view_name"].ToString());

                views.Add(
                    translator.OracleViewCollectionToView(
                        view,
                        GetViewColumnsCollection(connection, viewName)));
            }

            return views;
        }

        /// <summary>
        /// Gets function, stored procedures, and packages.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public override List<StoredProcedure> GetStoredProcedures(DbConnection connection)
        {
            // IMPORTANT: Must set the user ID before this public implementation used!
            _userName = GetUserName(connection).ToUpperInvariant();

            List<StoredProcedure> storedProcedures = new List<StoredProcedure>();
            DataTable storedProcedureCollection = GetStoredProceduresCollection(connection);

            foreach (DataRow procedure in storedProcedureCollection.Rows)
            {
               storedProcedures.Add(OracleStoredProcedureTranslator.OracleStoredProcedureCollectionToStoreProcedure(procedure));
            }

            return storedProcedures;
        }

       public StoredProcedure GetStoredProcedure(DbConnection connection, String procedureName)
       {
          throw new NotImplementedException(); //TODO: Review need for this method
 
       }

        /// <summary>
        /// Gets the parameters of a procedure.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="procedure"></param>
        /// <returns></returns>
        public override List<Parameter> GetSPResultSet(DbConnection connection, StoredProcedure procedure)
        {
           // IMPORTANT: Must set the user ID before this public implementation used!
           _userName = GetUserName(connection).ToUpperInvariant();

            DataTable storedProcedureParameters = GetStoredProcedureParametersCollection(
                                                    connection,
                                                    _userName,
                                                    ((OracleStoredProcedure)procedure).PackageName,
                                                    procedure.Name
                                                    );
           
           return OracleStoredProcedureTranslator.OracleParametersCollectionToOracleParameters(
              storedProcedureParameters);
        }
        #endregion

        #region Private Implementation

        private DataTable GetViewsCollection(DbConnection connection)
        {
            string[] restrictions = new string[2] { _userName, null };
            DataTable views = connection.GetSchema("Views", restrictions);
            return views;
        }

        private DataTable GetViewColumnsCollection(DbConnection connection, string viewName)
        {
            string[] restrictions = new string[3] { _userName, viewName, null }; // {owner, table/view, column}
            DataTable columns = connection.GetSchema("Columns", restrictions);
            columns.TableName = viewName;
            return columns;
        }
        private DataTable GetTablesCollection(DbConnection connection)
        {
            string[] restrictions = new string[2] { _userName, null }; //{owner, table}
            DataTable tables = connection.GetSchema("Tables", restrictions);

            return tables;
        }
        private DataTable GetTableColumnsCollection(DbConnection connection, string tableName)
        {

            string[] restrictions = new string[3] { _userName, tableName, null };
            DataTable columns = connection.GetSchema("Columns", restrictions);
            columns.TableName = tableName;

            columns.Columns.Add(CreateNewColumn("IsPrimaryKey", typeof(bool), false, false));
            columns.Columns.Add(CreateNewColumn("IsUniqueKey", typeof(bool), false, false));
            columns.Columns.Add(CreateNewColumn("IsReadOnly", typeof(bool), false, false));
            columns.Columns.Add(CreateNewColumn("IsIdentity", typeof(bool), false, false));
            columns.Columns["Length"].ColumnName = "Data_Length";
            columns.Columns["DataType"].ColumnName = "Data_Type";
            columns.Columns["Scale"].ColumnName = "Data_Scale";
            columns.Columns["Precision"].ColumnName = "Data_Precision";

            foreach (DataRow row in columns.Rows)
            {
                row["IsPrimaryKey"] = IsPrimaryKey(connection, tableName, row["Column_name"].ToString());
                row["IsUniqueKey"] = IsUniqueKey(connection, tableName, row["Column_name"].ToString());
            }
            return columns;
        }
        private DataTable GetTableForeignKeysCollection(DbConnection connection, string tableName)
        {
            string[] restrictions = new string[3] { _userName, tableName, null };
            DataTable foreignKeyCollumns = connection.GetSchema("ForeignKeyColumns", restrictions);
            return foreignKeyCollumns;
        }

        /// <summary>
        /// Returns a unique list of procedures and functions, whether standalone 
        /// or packaged, across schema owners.
        /// </summary>
        /// <param name="connection">A <see cref="DbConnection"/>.</param>
        /// <returns>A DataTable containing procedures and functions, both standalone and packaged.</returns>
        private DataTable GetStoredProceduresCollection(DbConnection connection)
        {

           const string sql = "SELECT DISTINCT PACKAGE_NAME, OBJECT_NAME FROM ALL_ARGUMENTS " +
                              "WHERE (OWNER = :OWNER) ORDER BY PACKAGE_NAME, OBJECT_NAME";
           DataTable table = new DataTable("Procedures");

           if (connection is Oracle.DataAccess.Client.OracleConnection)
           {
              ODP.OracleCommand selectCommand = new ODP.OracleCommand(sql, connection as Oracle.DataAccess.Client.OracleConnection);
              selectCommand.Parameters.Add(
                 "OWNER", Oracle.DataAccess.Client.OracleDbType.Varchar2, _userName, ParameterDirection.Input);
              
              ODP.OracleDataAdapter adapter = new ODP.OracleDataAdapter(selectCommand);
              adapter.Fill(table);
              adapter.Dispose();

           }
           else if (connection is System.Data.OracleClient.OracleConnection)
           {
              OracleCommand selectCommand = new OracleCommand(sql, connection as System.Data.OracleClient.OracleConnection);
              OracleParameter parameter = new OracleParameter("OWNER", _userName);
              parameter.DbType = DbType.String;
              parameter.Direction = ParameterDirection.Input;
              selectCommand.Parameters.Add(parameter);

              OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
              adapter.Fill(table);
              adapter.Dispose();
           }
           
           return table;
        }

        /// <summary>
        /// Returns a subset of the Arguments schema collection for a given procedure or function, 
        /// standalone or packaged.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="ownerName"></param>
        /// <param name="packageName"></param>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        private DataTable GetStoredProcedureParametersCollection(DbConnection connection, string ownerName, string packageName, string storedProcedureName)
        {
           string procedureName = RemovePackageNameFromProcedureName(storedProcedureName);
           // Restriction sequence: Owner, PackageName, ObjectName, ArgumentName
           string[] restrictions = new string[4] { ownerName, packageName, procedureName, null };
           return connection.GetSchema("Arguments", restrictions);

        }

        private List<Parameter> GetProcedureResultSchema(DataTable schema)
        {
            List<Parameter> parameters = new List<Parameter>();

            foreach (DataRow row1 in schema.Rows)
            {
                Type type1 = (Type)row1["Data_Type"];
                int num1 = -1;
                if (type1 == typeof(string))
                {
                    num1 = (int)row1["Data_Length"];
                }

                OracleDbParameter parameter = new OracleDbParameter();
                parameter.DBName = OracleTranslatorHelper.GetQualifiedName((string)row1["Column_Name"]);
                parameter.Direction = ParameterDirection.ReturnValue;

                OracleDbDataType dbDataType = new OracleDbDataType();
                dbDataType.Size = num1;
                dbDataType.ProviderType = OracleDataTypeConverter.String2DatabaseType(row1["Data_Type"].ToString());
                dbDataType.Type = type1;
                parameter.DbDataType = dbDataType;

                parameters.Add(parameter);
            }
            return parameters;
        }

        private bool IsForeignKeyPresent(DataTable foreignKeys, string contraintName, string schema, string tableName)
        {
            foreach (DataRow row in foreignKeys.Rows)
            {
                if (row["owner"].ToString() == schema && row["table_name"].ToString() == tableName)
                {
                    if (row["constraint_name"].ToString() == contraintName)
                    {
                        return true;
                    }
                }
            }

            return false;
        }

        private bool IsPrimaryKey(DbConnection connection, string table, string column)
        {
            //actual implementation
            string[] restrictions = new string[5] { null, null, null, table, column };
            DataTable indexColumns = connection.GetSchema("IndexColumns", restrictions);

            foreach (DataRow index in indexColumns.Rows)
            {
                string constraint = index["index_name"].ToString();
                string[] restrictions1 = new string[3] { null, table, constraint };
                DataTable primaryKeys = connection.GetSchema("PrimaryKeys", restrictions1);
                if (primaryKeys.Rows.Count > 0)
                    return true;
            }
            return false;
        }

        private bool IsUniqueKey(DbConnection connection, string table, string column)
        {
            //actual implementation
            string[] restrictions = new string[5] { null, null, null, table, column };
            DataTable indexColumns = connection.GetSchema("IndexColumns", restrictions);

            foreach (DataRow index in indexColumns.Rows)
            {
                string constraint = index["index_name"].ToString();
                string[] restrictions1 = new string[3] { null, table, constraint };
                DataTable primaryKeys = connection.GetSchema("UniqueKeys", restrictions1);
                if (primaryKeys.Rows.Count > 0)
                    return true;
            }
            return false;
        }


        /// <summary>
        /// Always return false.  There is no concept of identity column in Oracle.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        private bool IsIdentity(String column)
        {
            return false;
        }

        /// <summary>
        /// Always return false.  There is no concept of read-only column in Oracle.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>       
        private bool IsReadOnly(String column)
        {
            return false;
        }

        private DataColumn CreateNewColumn(string columnName, Type dataType,
                bool allowDBNull, object defaultValue)
        {
            DataColumn col = new DataColumn();
            col.ColumnName = columnName;
            col.DataType = dataType;
            col.AllowDBNull = allowDBNull;
            col.DefaultValue = defaultValue;
            return col;
        }

        /// <summary>
        /// Determines the user name from the connection.ConnectionString property.
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        private String GetUserName(DbConnection connection)
        {
            if (String.IsNullOrEmpty(this._userName))
            {
                if (connection is System.Data.OracleClient.OracleConnection)
                {
                    System.Data.OracleClient.OracleConnectionStringBuilder connStringBuilder =
                        new System.Data.OracleClient.OracleConnectionStringBuilder(
                            connection.ConnectionString);

                    _userName = connStringBuilder.UserID.ToUpperInvariant();

                }
                else if (connection is Oracle.DataAccess.Client.OracleConnection)
                {
                    Oracle.DataAccess.Client.OracleConnectionStringBuilder connStringBuilder =
                        new Oracle.DataAccess.Client.OracleConnectionStringBuilder(
                            connection.ConnectionString);

                    _userName = connStringBuilder.UserID.ToUpperInvariant();
                }
                else
                {
                    throw new ApplicationException("Unable to determine user name from connection.");
                }
            }

            return this._userName;
        }

       /// <summary>
       /// Removes package name from procedure name, if found.
       /// </summary>
       /// <param name="packageProcedureName"></param>
       /// <returns></returns>
       private String RemovePackageNameFromProcedureName(String packageProcedureName)
       {
          string packageName;
          string objectName;

          if (packageProcedureName.Contains("."))
          {
             string[] temp = packageProcedureName.Split(new char[] { char.Parse(".") });
             packageName = temp[0];
             objectName = temp[1];
          }
          else
          {
             objectName = packageProcedureName;
          }

          return objectName;
       }

        #endregion
    }
}